package com.dy.druid;


import com.dy.druid.model.PropertyCustomer;
import org.apache.commons.lang3.StringUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class InsertMode {

    static Connection conn = null;

    public static void initConn() throws ClassNotFoundException, SQLException {

        String url = "jdbc:mysql://10.6.5.42:3306/tencentscore?user=root&password=adminroot&useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=UTC";

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("成功加载MySQL驱动程序");
            // 一个Connection代表一个数据库连接
            conn = DriverManager.getConnection(url);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void insert() {
        // 开时时间
        long bTime1 = System.currentTimeMillis();

        SimpleDateFormat formatter = new SimpleDateFormat("yyyy年-MM月dd日-HH时mm分ss秒");

        Date date = new Date(bTime1);

        System.out.println("start========>" + formatter.format(date));
        // sql前缀
        String prefix = "INSERT INTO property_customer (account_id, create_date, description, hash_phone, hashqq, lastmodified_time, name, score, status, type, upload, user_property_set_id) VALUES";

        try {
            // 保存sql后缀
            StringBuffer suffix = new StringBuffer();
            // 设置事务为非自动提交
            conn.setAutoCommit(false);
            // Statement st = conn.createStatement();
            // 比起st，pst会更好些
            PreparedStatement pst = conn.prepareStatement(prefix);
            // 外层循环，总提交事务次数
            for (int i = 1; i <= 1; i++) {
                // 第次提交步长
                try {
                    BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(new File("C:\\tx_updata\\1.txt")),
                            "UTF-8"));
                    String lineTxt = null;
                    List<PropertyCustomer> list = new ArrayList<>();
                    while ((lineTxt = br.readLine()) != null) {
                        if (StringUtils.isNotBlank(lineTxt)) {
//                            suffix.append("(" + 1 + ", NOW() , '1' , '" + lineTxt + "',NULL, NOW(), 'markorJ', NULL, 'FAIL', 'J0', b'0', " + 1 + "),");
                            suffix.append("(" + 1 + ", NOW() , '1' , '" + lineTxt + "',NULL, NOW(), 'markorJ', NULL, 'SUCCESS', 'J1', b'0', " + 1 + "),");
//                            suffix.append("(" + 1 + ", NOW() , '模型二' , '" + lineTxt + "',NULL, NOW(), 'markorI', NULL, 'FAIL', 'I0', b'0', " + 4545 + "),");
//                            suffix.append("(" + 1 + ", NOW() , '模型二' , '" + lineTxt + "',NULL, NOW(), 'markorI', NULL, 'SUCCESS', 'I1', b'0', " + 4545 + "),");
                        }
                    }
                    br.close();
                } catch (Exception e) {
                    System.err.println("read errors :" + e);
                }
                // 构建完整sql
                String sql = prefix + suffix.substring(0, suffix.length() - 1);
                // 添加执行sql
                pst.addBatch(sql);
                // 执行操作
                pst.executeBatch();
                // 提交事务
                conn.commit();
                // 清空上一次添加的数据
                suffix = new StringBuffer();
            }
            // 头等连接
            pst.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //关闭总计时
        long eTime1 = System.currentTimeMillis();
        Date date1 = new Date(eTime1);

        System.out.println("end========>" + formatter.format(date1));
        //输出
        System.out.println("插入100W数据共耗时：" + (eTime1 - bTime1));
    }


    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        initConn();
        insert();

    }
}
